<?php

Yii::import("application.extensions.yiiexel.YiiExcel");

class ExpensesController extends Controller {

    /**
     * @var string the default layout for the views. Defaults to '//layouts/column2', meaning
     * using two-column layout. See 'protected/views/layouts/column2.php'.
     */
    public $layout = '//layouts/column1';

    /**
     * @return array action filters
     */
    public function filters() {
        return array(
            'accessControl', // perform access control for CRUD operations
            'postOnly + delete', // we only allow deletion via POST request
        );
    }

    /**
     * Specifies the access control rules.
     * This method is used by the 'accessControl' filter.
     * @return array access control rules
     */
    public function accessRules() {
        return array(
            array('allow', // allow all users to perform 'index' and 'view' actions
                'actions' => array('index', 'view', 'addnewexpense', 'getconvertionrate', 'loadreceipt', 'search', 'delete', 'undelete', 'editmultipleexpense', 'addexpensestoreport', 'exportexcel', 'newexpense', 'editexpense', 'deleteeachexpense', 'editexpenseprevious', 'editexpensenext'),
                'users' => array('*'),
            ),
            array('allow', // allow authenticated user to perform 'create' and 'update' actions
                'actions' => array('create', 'update'),
                'users' => array('@'),
            ),
            array('allow', // allow admin user to perform 'admin' and 'delete' actions
                'actions' => array('admin', 'delete'),
                'users' => array('admin'),
            ),
            array('deny', // deny all users
                'users' => array('*'),
            ),
        );
    }

    /**
     * Displays a particular model.
     * @param integer $id the ID of the model to be displayed
     */
    public function actionView($id) {
        $this->render('view', array(
            'model' => $this->loadModel($id),
        ));
    }

    private function getSearchResults($page = 1) {
        $merchant = isset($_REQUEST['merchant']) ? trim($_REQUEST['merchant']) : '';
        $from = isset($_REQUEST['startDate']) ? $_REQUEST['startDate'] : date('Y-m-d G:i:s', PHP_INT_MAX + 1);
        $to = isset($_REQUEST['endDate']) ? $_REQUEST['endDate'] + ' 23:59:59.999' : date('Y-m-d G:i:s', PHP_INT_MAX);
        $expenseStatus = isset($_REQUEST['expense_status']) ? $_REQUEST['expense_status'] : 'All';
        $category = isset($_REQUEST['expense_filter_categories']) ? $_REQUEST['expense_filter_categories'] : -1;
        $pageSize = Yii::app()->params['expensePageSize'];
        $customerId = -1;
        $userId = Yii::app()->user->id;

        $filterSelector = isset($_REQUEST['expense_filter_type']) ? $_REQUEST['expense_filter_type'] : 'unreported';
        $command = Yii::app()->db->createCommand("EXEC stp_GetAllExpenseLimited :Merchant,:From, :To,:FilterSelector,:ExpenseStatus,:Category,:UserId,:PageSize,:Page");
        $command->bindValue(':Merchant', $merchant, PDO::PARAM_STR);
        $command->bindValue(':From', $from, PDO::PARAM_STR);
        $command->bindValue(':To', $to, PDO::PARAM_STR);
        $command->bindValue(':FilterSelector', $filterSelector, PDO::PARAM_STR);
        $command->bindValue(':ExpenseStatus', $expenseStatus, PDO::PARAM_STR);
        $command->bindValue(':UserId', $userId, PDO::PARAM_INT);
        $command->bindValue(':Category', $category, PDO::PARAM_INT);
        $command->bindValue(':PageSize', $pageSize, PDO::PARAM_INT);
        $command->bindValue(':Page', $page, PDO::PARAM_INT);
        $rows = $command->queryAll();
        return $rows != null ? $rows : array();
    }

    /**
     *
     * Execute to search the receipt by the searching condition
     * @param int $page
     */
    public function actionSearch() {
        $page = isset($_POST['expense_page']) ? $_POST['expense_page'] : 1;
        $currencies = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
        $this->layout = '//layouts/main';
        $pageSize = Yii::app()->params['expensePageSize'];
        $rows = $this->getSearchResults($page);
        $userId = Yii::app()->user->id;
        $defaultCurrency = Yii::app()->db->createCommand("EXEC stp_GetDefaultCurrencyByUserId $userId")->queryScalar();
        $this->render('search', array(
            'rows' => $rows,
            'page' => $page,
            'limit' => $pageSize,
            'totalItem' => isset($rows[0]['TotalCount']) ? $rows[0]['TotalCount'] : 0,
            'currencies' => $currencies,
            'defaultCurrency' => $defaultCurrency
        ));
    }

    /**
     *
     * Edit multiple expense
     */
    public function actionEditMultipleExpense() {
        if (isset($_POST['multipleString'])) {
            $multipleString = $_POST['multipleString'];
            $customer = $_POST['customer'];
            $date = $_POST['created'];
            $currency = $_POST['currency'];
            $total = $_POST['amount'];
            $category = $_POST['category'];
            $description = $_POST['description'];
            $comment = $_POST['comment'];
            $customerClaim = isset($_POST['customer_claim']);
            $billable = isset($_POST['billable']);
            $command = Yii::app()->db->createCommand("EXEC stp_SetEditMutilpleExpenses :multipleString,:customer,:date,:currency,:total,:category,:description,:comment,:customerClaim,:billable");
            $command->bindValue(':multipleString', $multipleString, PDO::PARAM_INT);
            $command->bindValue(':customer', $customer, PDO::PARAM_INT);
            $command->bindValue(':date', $date, PDO::PARAM_STR);
            $command->bindValue(':currency', $currency, PDO::PARAM_INT);
            $command->bindValue(':total', $total, PDO::PARAM_STR);
            $command->bindValue(':category', $category, PDO::PARAM_INT);
            $command->bindValue(':description', $description, PDO::PARAM_STR);
            $command->bindValue(':comment', $comment, PDO::PARAM_STR);
            $command->bindValue(':customerClaim', $customerClaim, PDO::PARAM_BOOL);
            $command->bindValue(':billable', $billable, PDO::PARAM_BOOL);
            $command->execute();
        }
    }

    /**
     *
     * Add new expense
     */
    public function actionAddNewExpense() {
        if (Yii::app()->request->isPostRequest && isset($_POST['tab'])) {
            $result = array('success' => true);
            switch ($_POST['tab']) {
                case 'expense_tab':
                    $customerId = $_POST['customer'];
                    $SRFNo = $_POST['SRF'];
                    $billable = isset($_POST['billable']) ? true : false;
                    $merchant = $_POST['merchant'];
                    $date = $_POST['created'];
                    $currencyId = $_POST['expenseCurrency'];
                    $total = $_POST['amount'];
                    $claimCustomer = isset($_POST['claimCustomer']);
                    $categoryId = $_POST['category'];
                    $itemDescription = $_POST['item_description'];
                    $comment = $_POST['comment'];
                    $reportId = ($_POST['reportID'] != '-1') ? $_POST['reportID'] : NULL;
                    $receiptId = $_POST['receiptId'];
                    $createAt = $_POST['created'];
                    try {

                        $command = Yii::app()->db->createCommand("EXEC stp_InsertNewExpenseTab
																:customerId
															   ,:SRFNo
															   ,:billable
															   ,:merchant
															   ,:currencyId
															   ,:total
															   ,:claimCustomer
													           ,:categoryId
															   ,:itemDescription
															   ,:comment
													           ,:reportId
													           ,:receiptId
													           ,:userId
													           ,:createAt");
                        $command->bindValue(':customerId', $customerId, PDO::PARAM_INT);
                        $command->bindValue(':SRFNo', $SRFNo, PDO::PARAM_STR);
                        $command->bindValue(':billable', $billable, PDO::PARAM_BOOL);
                        $command->bindValue(':merchant', $merchant, PDO::PARAM_STR);
                        $command->bindValue(':currencyId', $currencyId, PDO::PARAM_INT);
                        $command->bindValue(':total', $total, PDO::PARAM_STR);
                        $command->bindValue(':claimCustomer', $claimCustomer, PDO::PARAM_BOOL);
                        $command->bindValue(':categoryId', $categoryId, PDO::PARAM_STR);
                        $command->bindValue(':itemDescription', $itemDescription, PDO::PARAM_STR);
                        $command->bindValue(':comment', $comment, PDO::PARAM_STR);
                        $command->bindValue(':reportId', $reportId, PDO::PARAM_INT);
                        $command->bindValue(':receiptId', $receiptId, PDO::PARAM_INT);
                        $command->bindValue(':userId', Yii::app()->user->id, PDO::PARAM_INT);
                        $command->bindValue(':createAt', $createAt, PDO::PARAM_STR);
                        $command->execute();
                    } catch (Exception $e) {
                        $result['success'] = false;
                        $result['message']['executeSql'] = 'Error cannot execute insert data';
                    }

                    break;
                case 'mileage_tab':

                    $customerId = $_POST['customer'];
                    $SRFNo = $_POST['SRF'];
                    $billable = isset($_POST['billable']) ? true : false;
                    $fromPosition = $_POST['fromPosition'];
                    $toPosition = $_POST['toPosition'];
                    $distance = $_POST['miles'];
                    $unit = $_POST['unit'];
                    $merchant = $_POST['merchant'];
                    $date = $_POST['created'];
                    $currencyId = $_POST['expenseCurrency'];
                    $total = $_POST['amount'];
                    $claimCustomer = isset($_POST['claimCustomer']);
                    $categoryId = $_POST['category'];
                    $itemDescription = $_POST['item_description'];
                    $comment = $_POST['comment'];
                    $reportId = ($_POST['reportID'] != '-1') ? $_POST['reportID'] : null;
                    $receiptId = $_POST['receiptId'];


                    try {
                        $command = Yii::app()->db->createCommand("EXEC stp_InsertNewMileageTab
																	:customerId
																   ,:SRFNo
																   ,:billable
																   ,:merchant
																   ,:date
																   ,:fromPosition
																   ,:toPosition
																   ,:distance
																   ,:unit
																   ,:currencyId
																   ,:total
																   ,:claimCustomer
														           ,:categoryId
																   ,:itemDescription
																   ,:comment
														           ,:reportId
														           ,:receiptId
														           ,:userId
														           ");
                        $command->bindValue(':customerId', $customerId, PDO::PARAM_INT);
                        $command->bindValue(':SRFNo', $SRFNo, PDO::PARAM_STR);
                        $command->bindValue(':billable', $billable, PDO::PARAM_BOOL);
                        $command->bindValue(':merchant', $merchant, PDO::PARAM_STR);
                        $command->bindValue(':date', $date, PDO::PARAM_STR);
                        $command->bindValue(':fromPosition', $fromPosition, PDO::PARAM_STR);
                        $command->bindValue(':toPosition', $toPosition, PDO::PARAM_STR);
                        $command->bindValue(':distance', $distance, PDO::PARAM_STR);
                        $command->bindValue(':unit', $unit, PDO::PARAM_INT);
                        $command->bindValue(':currencyId', $currencyId, PDO::PARAM_INT);
                        $command->bindValue(':total', $total, PDO::PARAM_STR);
                        $command->bindValue(':claimCustomer', $claimCustomer, PDO::PARAM_BOOL);
                        $command->bindValue(':categoryId', $categoryId, PDO::PARAM_INT);
                        $command->bindValue(':itemDescription', $itemDescription, PDO::PARAM_STR);
                        $command->bindValue(':comment', $comment, PDO::PARAM_STR);
                        $command->bindValue(':reportId', $reportId, PDO::PARAM_INT);
                        $command->bindValue(':receiptId', $receiptId, PDO::PARAM_INT);
                        $command->bindValue(':userId', Yii::app()->user->id, PDO::PARAM_INT);

                        $command->execute();
                    } catch (Exception $e) {
                        $result['success'] = false;
                        $result['message']['executeSql'] = 'Error cannot execute insert data';
                    }

                    break;
                case 'time_tab':
                    $customerId = $_POST['customer'];
                    $SRFNo = $_POST['SRF'];
                    $billable = isset($_POST['billable']) ? true : false;
                    $hour = $_POST['hour'];
                    $hourRate = $_POST['rate'];
                    $date = $_POST['created'];
                    $merchant = $_POST['merchant'];
                    $currencyId = $_POST['expenseCurrency'];
                    $total = $_POST['amount'];
                    $claimCustomer = isset($_POST['claimCustomer']);
                    $categoryId = $_POST['category'];
                    $itemDescription = $_POST['item_description'];
                    $comment = $_POST['comment'];
                    $reportId = ($_POST['reportID'] != '-1') ? $_POST['reportID'] : NULL;
                    $receiptId = isset($_POST['receiptId']) ? $_POST['receiptId'] : '';
                    $createAt = $_POST['created'];
                    try {
                        $command = Yii::app()->db->createCommand("EXEC stp_InsertNewExpenseTimeTab
																	:customerId
																   ,:SRFNo
																   ,:billable
																   ,:merchant
																   ,:date
																   ,:hour
																   ,:hourRate
																   ,:currencyId
																   ,:total
																   ,:claimCustomer
														           ,:categoryId
																   ,:itemDescription
																   ,:comment
														           ,:reportId
														           ,:receiptId
														           ,:userId
														           ");
                        $command->bindValue(':customerId', $customerId, PDO::PARAM_INT);
                        $command->bindValue(':SRFNo', $SRFNo, PDO::PARAM_STR);
                        $command->bindValue(':billable', $billable, PDO::PARAM_BOOL);
                        $command->bindValue(':merchant', $merchant, PDO::PARAM_STR);
                        $command->bindValue(':date', $date, PDO::PARAM_STR);
                        $command->bindValue(':hour', $hour, PDO::PARAM_STR);
                        $command->bindValue(':hourRate', $hourRate, PDO::PARAM_INT);
                        $command->bindValue(':currencyId', $currencyId, PDO::PARAM_INT);
                        $command->bindValue(':total', $total, PDO::PARAM_STR);
                        $command->bindValue(':claimCustomer', $claimCustomer, PDO::PARAM_BOOL);
                        $command->bindValue(':categoryId', $categoryId, PDO::PARAM_STR);
                        $command->bindValue(':itemDescription', $itemDescription, PDO::PARAM_STR);
                        $command->bindValue(':comment', $comment, PDO::PARAM_STR);
                        $command->bindValue(':reportId', $reportId, PDO::PARAM_INT);
                        $command->bindValue(':receiptId', $receiptId, PDO::PARAM_INT);
                        $command->bindValue(':userId', Yii::app()->user->id, PDO::PARAM_INT);
                        $command->execute();
                    } catch (Exception $e) {
                        $result['success'] = false;
                        $result['message']['executeSql'] = 'Error cannot execute insert data';
                    }

                    break;
                case 'multiple_tab':
                    $numberRows = (int) $_POST['multiple_number_row'];

                    try {
                        for ($i = 0; $i < $numberRows; $i++) {
                            $customerId = $_POST['customer'];
                            $SRFNo = $_POST['SRF'];
                            $billable = isset($_POST['billable']) ? true : false;
                            $date = $_POST['created_' . $i];
                            $merchant = $_POST['merchant_' . $i];
                            $claimCustomer = isset($_POST['customerClaim_' . $i]) ? true : false;
                            $total = $_POST['amount_' . $i];
                            $currencyId = $_POST['currency_' . $i];
                            $categoryId = $_POST['category_' . $i];
                            $itemDescription = $_POST['description_' . $i];
                            $comment = $_POST['comment_' . $i];
                            $reportId = null;
                            $receiptId = $_POST['mutilReceiptId_' . $i];
                            $createAt = $_POST['created_' . $i];
                            $command = Yii::app()->db->createCommand("EXEC stp_InsertNewExpenseTab
																		:customerId
																	   ,:SRFNo
																	   ,:billable
																	   ,:merchant
																	   ,:currencyId
																	   ,:total
																	   ,:claimCustomer
															           ,:categoryId
																	   ,:itemDescription
																	   ,:comment
															           ,:reportId
															           ,:receiptId
															           ,:userId
															           ,:createAt");
                            $command->bindValue(':customerId', $customerId, PDO::PARAM_INT);
                            $command->bindValue(':SRFNo', $SRFNo, PDO::PARAM_STR);
                            $command->bindValue(':billable', $billable, PDO::PARAM_BOOL);
                            $command->bindValue(':merchant', $merchant, PDO::PARAM_STR);
                            $command->bindValue(':currencyId', $currencyId, PDO::PARAM_INT);
                            $command->bindValue(':total', $total, PDO::PARAM_STR);
                            $command->bindValue(':claimCustomer', $claimCustomer, PDO::PARAM_BOOL);
                            $command->bindValue(':categoryId', $categoryId, PDO::PARAM_STR);
                            $command->bindValue(':itemDescription', $itemDescription, PDO::PARAM_STR);
                            $command->bindValue(':comment', $comment, PDO::PARAM_STR);
                            $command->bindValue(':reportId', $reportId, PDO::PARAM_INT);
                            $command->bindValue(':receiptId', $receiptId, PDO::PARAM_INT);
                            $command->bindValue(':userId', Yii::app()->user->id, PDO::PARAM_INT);
                            $command->bindValue(':createAt', $createAt, PDO::PARAM_STR);
                            $command->execute();
                        }
                    } catch (Exception $e) {
                        $result['success'] = false;
                        $result['message']['executeSql'] = 'Error cannot execute insert data';
                    }

                    break;
            }
        }
        print_r(json_encode($result));
    }

    /**
     * Creates a new model.
     * If creation is successful, the browser will be redirected to the 'view' page.
     */
    public function actionCreate() {
        $model = new Expense;

        // Uncomment the following line if AJAX validation is needed
        // $this->performAjaxValidation($model);

        if (isset($_POST['Expense'])) {
            $model->attributes = $_POST['Expense'];
            if ($model->save())
                $this->redirect(array('view', 'id' => $model->ExpenseId));
        }

        $this->render('create', array(
            'model' => $model,
        ));
    }

    /**
     * Updates a particular model.
     * If update is successful, the browser will be redirected to the 'view' page.
     * @param integer $id the ID of the model to be updated
     */
    public function actionUpdate($id) {
        $model = $this->loadModel($id);

        // Uncomment the following line if AJAX validation is needed
        // $this->performAjaxValidation($model);

        if (isset($_POST['Expense'])) {
            $model->attributes = $_POST['Expense'];
            if ($model->save())
                $this->redirect(array('view', 'id' => $model->ExpenseId));
        }

        $this->render('update', array(
            'model' => $model,
        ));
    }

    /**
     * Lists all models.
     */
    public function actionIndex() {
        $customers = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCustomers")->queryAll();
        $currencies = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
        $categories = Yii::app()->db->createCommand("EXEC stp_GetAllActivedExpenseCategory")->queryAll();
        $reports = Yii::app()->db->createCommand("EXEC stp_GetAllActivedReports")->queryAll();
        $pageSize = $pageSize = Yii::app()->params['expensePageSize'];
        $page = 1;
        $userId = Yii::app()->user->id;
        $totalItem = Yii::app()->db->createCommand("EXEC stp_GetTotalExpenseUnSubmittedtLimited $userId")->queryAll();
        $command = Yii::app()->db->createCommand("EXEC stp_GetAllExpenseUnSubmittedtLimited :userId, :pageSize, :page ");
        $command->bindValue(':userId', $userId, PDO::PARAM_INT);
        $command->bindValue(':pageSize', $pageSize, PDO::PARAM_INT);
        $command->bindValue(':page', $page, PDO::PARAM_INT);
        $rows = $command->queryAll();
        $defaultCurrency = Yii::app()->db->createCommand("EXEC stp_GetDefaultCurrencyByUserId $userId ")->queryScalar();

        $this->render('index', array(
            'customers' => $customers,
            'currencies' => $currencies,
            'categories' => $categories,
            'reports' => $reports,
            'rows' => $rows,
            'page' => $page,
            'limit' => $pageSize,
            'totalItem' => isset($totalItem[0]['Total']) ? $totalItem[0]['Total'] : 0,
            'defaultCurrency' => $defaultCurrency
        ));
    }

    /**
     *
     * Delete expense by check all
     */
    public function actionDelete() {
        if (isset($_POST['deleteString'])) {
            $deleteString = $_POST['deleteString'];
            $command = Yii::app()->db->createCommand("EXEC stp_SetDeleteFlgExpenses '$deleteString'")->execute();
        }
    }

    /**
     * Delete each expense
     */
    public function actionDeleteEachExpense() {
        if (isset($_POST['deleteExpenseId'])) {
            $deleteExpenseId = $_POST['deleteExpenseId'];
            $command = Yii::app()->db->createCommand("EXEC stp_SetDeleteFlgEachExpense $deleteExpenseId ")->execute();
        }
    }

    /**
     *
     * Undelete receipt by check all
     */
    public function actionUndelete() {
        if (isset($_POST['deleteString'])) {
            $deleteString = $_POST['deleteString'];
            $command = Yii::app()->db->createCommand("EXEC stp_SetUndeleteFlgExpenses '$deleteString'")->execute();
        }
    }

    /**
     * Manages all models.
     */
    public function actionAdmin() {
        $model = new Expense('search');
        $model->unsetAttributes();  // clear any default values
        if (isset($_GET['Expense']))
            $model->attributes = $_GET['Expense'];

        $this->render('admin', array(
            'model' => $model,
        ));
    }

    public function actionLoadReceipt() {
        $this->layout = '//layouts/main';
        $from = date('Y-m-d G:i:s', PHP_INT_MAX + 1);
        $to = date('Y-m-d G:i:s', PHP_INT_MAX);
        $attachedReceipt = 'UNATTACHED';
        $pageSize = 4;
        $page = 1;
        $filterSelector = '';
        $command = Yii::app()->db->createCommand("EXEC stp_GetAllReceiptLimited :From, :To,:FilterSelector,:AttachedReceipt,:PageSize,:Page");
        $command->bindValue(':From', $from, PDO::PARAM_STR);
        $command->bindValue('To', $to, PDO::PARAM_STR);
        $command->bindValue(':FilterSelector', $filterSelector, PDO::PARAM_STR);
        $command->bindValue(':AttachedReceipt', $attachedReceipt, PDO::PARAM_STR);
        $command->bindValue(':PageSize', $pageSize, PDO::PARAM_INT);
        $command->bindValue(':Page', $page, PDO::PARAM_INT);
        $rows = $command->queryAll();
        $reports = Yii::app()->db->createCommand("EXEC stp_GetAllActivedReports")->queryAll();

        $this->render('loadReceipt', array(
            'rows' => $rows,
            'reports' => $reports,
            'page' => $page,
            'limit' => $pageSize,
            'totalItem' => isset($rows[0]['TotalCount']) ? $rows[0]['TotalCount'] : 0
        ));
    }

    /**
     * Returns the data model based on the primary key given in the GET variable.
     * If the data model is not found, an HTTP exception will be raised.
     * @param integer $id the ID of the model to be loaded
     * @return Expense the loaded model
     * @throws CHttpException
     */
    public function loadModel($id) {
        $model = Expense::model()->findByPk($id);
        if ($model === null)
            throw new CHttpException(404, 'The requested page does not exist.');
        return $model;
    }

    /**
     * Performs the AJAX validation.
     * @param Expense $model the model to be validated
     */
    protected function performAjaxValidation($model) {
        if (isset($_POST['ajax']) && $_POST['ajax'] === 'expense-form') {
            echo CActiveForm::validate($model);
            Yii::app()->end();
        }
    }

    /**
     *
     * Add expenses to report
     */
    public function actionAddExpensesToReport() {
        if (isset($_POST['expenseString'])) {
            $expenseString = $_POST['expenseString'];
            $reportId = $_POST['reportId'];
            echo $expenseString . '_' . $reportId;
            $command = Yii::app()->db->createCommand("EXEC stp_AddExpensesToReport '$expenseString','$reportId'")->execute();
        }
    }

    /**
     *
     * Export expenses to Excel file
     * @throws CHttpException
     */
    public function actionExportExcel() {
        $rows = $this->getSearchResults(1);
        $r = new YiiReport(array('template' => 'Bulk_Expense_Export.xls'));
        $r->load($rows);
        echo $r->render('excel2007', 'test.xls');
    }

    /**
     *
     * Create New EXPENSE
     */
    public function actionNewExpense() {

        $this->layout = '//layouts/main';
        $userId = Yii::app()->user->id;
        $customers = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCustomers")->queryAll();
        $currencies = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
        $categories = Yii::app()->db->createCommand("EXEC stp_GetAllActivedExpenseCategoryByUserId $userId")->queryAll();
        $reports = Yii::app()->db->createCommand("EXEC stp_GetAllActivedReports")->queryAll();
        $units = Yii::app()->db->createCommand("EXEC stp_GetAllActivedUnits")->queryAll();

        $defaultPreference = Yii::app()->db->createCommand("EXEC [stp_GetDefaultPreferenceByUserId] $userId")->queryAll();
        $this->render('newExpense', array(
            'customers' => $customers,
            'currencies' => $currencies,
            'categories' => $categories,
            'reports' => $reports,
            'units' => $units,
            'defaultPreference' => @$defaultPreference[0]
        ));
    }

    /**
     *
     * Edit a previous expense
     * @throws CHttpException
     */
    public function actionEditExpensePrevious() {
        if (isset($_POST['expense_id'])) {
            $this->layout = '//layouts/main';
            $expenseId = $_POST['expense_id'];
            $row = $_POST['row'];
            $total = $_POST['total'];
            $customers = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCustomers")->queryAll();
            $currencies = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
            $categories = Yii::app()->db->createCommand("EXEC stp_GetAllActivedExpenseCategory")->queryAll();
            $reports = Yii::app()->db->createCommand("EXEC stp_GetAllActivedReports")->queryAll();
            $units = Yii::app()->db->createCommand("EXEC stp_GetAllActivedUnits")->queryAll();
            $userId = Yii::app()->user->id;
            $defaultCurrency = Yii::app()->db->createCommand("EXEC stp_GetDefaultCurrencyByUserId $userId")->queryAll();
            $expense = Yii::app()->db->createCommand("EXEC stp_GetPreviousExpenseById $expenseId")->queryAll();
            $this->render('editExpense', array(
                'customers' => $customers,
                'currencies' => $currencies,
                'categories' => $categories,
                'reports' => $reports,
                'units' => $units,
                'defaultCurrency' => $defaultCurrency[0],
                'expense' => $expense[0],
                'total' => $total,
                'row' => $row
            ));
        }
    }

    /**
     *
     * Edit a next expense
     * @throws CHttpException
     */
    public function actionEditExpenseNext() {
        if (isset($_POST['expense_id'])) {
            $this->layout = '//layouts/main';
            $expenseId = $_POST['expense_id'];
            $row = $_POST['row'];
            $total = $_POST['total'];
            $customers = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCustomers")->queryAll();
            $currencies = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
            $categories = Yii::app()->db->createCommand("EXEC stp_GetAllActivedExpenseCategory")->queryAll();
            $reports = Yii::app()->db->createCommand("EXEC stp_GetAllActivedReports")->queryAll();
            $units = Yii::app()->db->createCommand("EXEC stp_GetAllActivedUnits")->queryAll();
            $userId = Yii::app()->user->id;
            $defaultCurrency = Yii::app()->db->createCommand("EXEC stp_GetDefaultCurrencyByUserId $userId")->queryAll();
            $expense = Yii::app()->db->createCommand("EXEC stp_GetNextExpenseById $expenseId")->queryAll();
            $this->render('editExpense', array(
                'customers' => $customers,
                'currencies' => $currencies,
                'categories' => $categories,
                'reports' => $reports,
                'units' => $units,
                'defaultCurrency' => $defaultCurrency[0],
                'expense' => $expense[0],
                'total' => $total,
                'row' => $row
            ));
        }
    }

    /**
     *
     * Edit a expense
     * @throws CHttpException
     */
    public function actionEditExpense() {
        if (isset($_POST['merchant'])) {
            $date = $_POST['date'];
            $merchant = $_POST['merchant'];
            $fromPosition = $_POST['fromPosition'];
            $toPosition = $_POST['toPosition'];
            $distance = $_POST['miles'];
            $unitId = $_POST['unit'];
            $currencyId = $_POST['expenseCurrency'];
            $total = $_POST['amount'];
            $claimCustomer = isset($_POST['claimCustomer']);
            $categoryId = $_POST['category'];
            $description = $_POST['item_description'];
            $comment = $_POST['comment'];
            $reportId = $_POST['reportID'];
            $receiptId = $_POST['receiptId'];
            $expenseId = $_POST['expenseId'];
            $command = Yii::app()->db->createCommand("EXEC stp_UpdateExpense :Date,
				:Merchant,
				:FromPosition,
				:ToPositiohn,
				:Distanceh,
				:UnitId,h
				:CurrenchyId,
				:Total,h
				:ClaimCusthomer,
				:CategoryId,
				:Description,
				:Comment,h
				:ReportId,h
				:ReceiptId,h
				:ExpenseIdh
		");
            $command->bindValue(':Date', $date, PDO::PARAM_STR);
            $command->bindValue(':Merchant', $merchant, PDO::PARAM_STR);
            $command->bindValue(':FromPosition', $fromPosition, PDO::PARAM_STR);
            $command->bindValue(':ToPosition', $toPosition, PDO::PARAM_STR);
            $command->bindValue(':Distance', $distance, PDO::PARAM_STR);
            $command->bindValue(':UnitId', $unitId, PDO::PARAM_INT);
            $command->bindValue(':CurrencyId', $currencyId, PDO::PARAM_INT);
            $command->bindValue(':Total', $total, PDO::PARAM_STR);
            $command->bindValue(':ClaimCustomer', $claimCustomer, PDO::PARAM_BOOL);
            $command->bindValue(':CategoryId', $categoryId, PDO::PARAM_INT);
            $command->bindValue(':Description', $description, PDO::PARAM_STR);
            $command->bindValue(':Comment', $comment, PDO::PARAM_STR);
            $command->bindValue(':ReportId', $reportId, PDO::PARAM_STR);
            $command->bindValue(':ReceiptId', $receiptId, PDO::PARAM_STR);
            $command->bindValue(':ExpenseId', $expenseId, PDO::PARAM_INT);
            $command->execute();
        } else {
            if (isset($_POST['expense_id'])) {
                $this->layout = '//layouts/main';
                $expenseId = $_POST['expense_id'];
                $row = $_POST['row'];
                $total = $_POST['total'];
                $customers = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCustomers")->queryAll();
                $currencies = Yii::app()->db->createCommand("EXEC stp_GetAllActivedCurrencies")->queryAll();
                $categories = Yii::app()->db->createCommand("EXEC stp_GetAllActivedExpenseCategory")->queryAll();
                $reports = Yii::app()->db->createCommand("EXEC stp_GetAllActivedReports")->queryAll();
                //$units = Yii::app()->db->createCommand("EXEC stp_GetAllActivedUnits")->queryAll();
                $units = array(array('Id' => 'km', 'UnitName' => 'Km'), array('Id' => 'm', 'UnitName' => 'Mile'));
                $userId = Yii::app()->user->id;
                $defaultCurrency = Yii::app()->db->createCommand("EXEC stp_GetDefaultCurrencyByUserId $userId")->queryAll();
                $expense = Yii::app()->db->createCommand("EXEC stp_GetExpenseById $expenseId")->queryAll();
                $this->render('editExpense', array(
                    'customers' => $customers,
                    'currencies' => $currencies,
                    'categories' => $categories,
                    'reports' => $reports,
                    'units' => $units,
                    'defaultCurrency' => $defaultCurrency[0],
                    'expense' => $expense[0],
                    'total' => $total,
                    'row' => $row
                ));
            }
        }
    }

    public function actionGetConvertionRate() {
        if (Yii::app()->request->isPostRequest) {
            if (isset($_POST['oldCurrency']) && isset($_POST['currentCurrency']) && is_numeric($_POST['oldCurrency']) && is_numeric($_POST['currentCurrency'])) {
                $command = Yii::app()->db->createCommand("EXEC stp_GetConvertionRate :oldCurrency,:currenCurrency");
                $command->bindValue(':oldCurrency', $_POST['oldCurrency'], PDO::PARAM_INT);
                $command->bindValue(':currenCurrency', $_POST['currentCurrency'], PDO::PARAM_INT);
                $result = $command->queryAll();

                echo isset($result[0]['ConvertionRate']) ? $result[0]['ConvertionRate'] : 0;
            }
        }
    }

}
